const { stringify } = require("querystring");
const { Database } = require("sqlite3");

function comlv() {
    const sqlite3 = require("sqlite3").verbose()
    const path = require('path');
    const dbName = path.join(__dirname, '../data/demo.db');
    var db;
    db = new sqlite3.Database(dbName,function(err){
        if(err) throw err;
    })
    db.run(` DELETE FROM lv`,function(err,row){
        if(err) throw err; 
    })
    db.run(` INSERT INTO lv  select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=1 
    UNION 
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=2
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=3
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=4
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=5
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=6
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=7
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=8
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=9
    UNION
    select  e_id,c_id,score,DENSE_RANK() OVER (order by score DESC) FROM qone WHERE e_id=10`,function(err,row){
        if(err) throw err; 
    })  
}

function getcomlv(){
    const sqlite3 = require("sqlite3").verbose()
    const path = require('path');
    const dbName = path.join(__dirname, '../data/demo.db');
    var db;
    db = new sqlite3.Database(dbName,function(err){
        if(err) throw err;
    })
    db.run(` UPDATE cafe SET c_level=(SELECT SUM(level) FROM lv WHERE c_id=1) WHERE c_id=1  `,function(err,row){
        if(err) throw err;
    })
    db.run(` UPDATE cafe SET c_level=(SELECT SUM(level) FROM lv WHERE c_id=2) WHERE c_id=2  `,function(err,row){
        if(err) throw err;
    })
    db.run(` UPDATE cafe SET c_level=(SELECT SUM(level) FROM lv WHERE c_id=3) WHERE c_id=3  `,function(err,row){
        if(err) throw err;
    })
    db.run(` UPDATE cafe SET c_level=(SELECT SUM(level) FROM lv WHERE c_id=4) WHERE c_id=4  `,function(err,row){
        if(err) throw err;
    })
    db.run(` UPDATE cafe SET c_level=(SELECT SUM(level) FROM lv WHERE c_id=5) WHERE c_id=5  `,function(err,row){
        if(err) throw err;
    })
    db.run(` UPDATE cafe SET c_level=(SELECT SUM(level) FROM lv WHERE c_id=6) WHERE c_id=6  `,function(err,row){
        if(err) throw err;
    })
}

function scomlv(){
    const sqlite3 = require("sqlite3").verbose()
    const path = require('path');
    const dbName = path.join(__dirname, '../data/demo.db');
    var db;
    db = new sqlite3.Database(dbName,function(err){
        if(err) throw err;
    })
    db.all(` select c_id,c_level FROM cafe `,function(err,row){
        if(err) throw err;
        console.log(JSON.stringify(row));
    })
}

comlv();
getcomlv();
scomlv()